package com.tjsinfo.tjsoa.addresslist.bo;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.LogicalExpression;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.SimpleExpression;

import com.tjsinfo.tjsoa.Dao.IHQLDao;
import com.tjsinfo.tjsoa.Dao.IJDBCDao;
import com.tjsinfo.tjsoa.Dao.IQbcDao;
import com.tjsinfo.tjsoa.addresslist.dao.IAddressListUserDAO;
import com.tjsinfo.tjsoa.addresslist.vo.AddressListUser;
import com.tjsinfo.tjsoa.addresslist.vo.AddresslistSend;
import com.tjsinfo.tjsoa.common.DayFormat;
import com.tjsinfo.tjsoa.system.vo.TSystemUser;

public class AddressListUserBo implements IAddressListUserBo {
	/* (non-Javadoc)
	 * @see com.tjsinfo.tjsoa.addresslist.AddressList_User.IAddressListUserBo#saveAddressListUser(com.tjsinfo.tjsoa.addresslist.AddressList_User.AddressListUser)
	 */
	private IAddressListUserDAO userDAO;
	private IJDBCDao jdbcDao;
	private IQbcDao qbcDao;
	private IHQLDao hqlDao;
	public IAddressListUserDAO getUserDAO() {
		return userDAO;
	}
	public void setUserDAO(IAddressListUserDAO userDAO) {
		this.userDAO = userDAO;
	}
	public IJDBCDao getJdbcDao() {
		return jdbcDao;
	}
	public void setJdbcDao(IJDBCDao jdbcDao) {
		this.jdbcDao = jdbcDao;
	}
	public IQbcDao getQbcDao() {
		return qbcDao;
	}
	public void setQbcDao(IQbcDao qbcDao) {
		this.qbcDao = qbcDao;
	}
	public IHQLDao getHqlDao() {
		return hqlDao;
	}
	public void setHqlDao(IHQLDao hqlDao) {
		this.hqlDao = hqlDao;
	}
	
	public String saveAddressListUser(AddressListUser user) {
		String i = userDAO.save(user);
		return i;
	}
	public void updateAddressListUser(AddressListUser user) {
		userDAO.update(user);
	}
	public AddressListUser getAddresssListUserById(String id) {
		AddressListUser user =  userDAO.findById(id);
		return user;
	}
	
	public List getAddresssListUserByUserId(Integer id) {
		String sql = "select tjsuser.userId,tjsuser.userName,listuser.createTime from t_addressList_User as listuser inner join t_system_user as tjsuser on listuser.tjsuser_id=tjsuser.userId where listuser.user_id=(select userid from t_addressList_systemUser where userId="+id+") and listuser.state='1' group by tjsuser.userId,tjsuser.userName,listuser.createTime order by listuser.createTime asc";
		ResultSet rs = jdbcDao.executeQuary(sql);
		List list = new ArrayList();
		
		try {
			while(rs.next()){
				TSystemUser user = new TSystemUser();
				int id1 = rs.getInt("userId");
				String userName = rs.getString("userName");
				
				user.setUserId(id1);
				user.setUserName(userName);
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		jdbcDao.closeRs();
		jdbcDao.closeStmt();
		jdbcDao.closeConn();
		return list;
	}
	
	public List getAddresssListUserByUserIdState(Integer id) {
		String sql = "select tjsuser.userId,tjsuser.userName,listuser.createTime from t_addressList_User as listuser inner join t_system_user as tjsuser on listuser.tjsuser_id=tjsuser.userId where listuser.user_id='"+id+"' group by tjsuser.userId,tjsuser.userName,listuser.createTime order by createTime asc";
		ResultSet rs = jdbcDao.executeQuary(sql);
		List list = new ArrayList();
		try {
			while(rs.next()){
				TSystemUser user = new TSystemUser();
				int id1 = rs.getInt("userId");
				String userName = rs.getString("userName");
				
				user.setUserId(id1);
				user.setUserName(userName);
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		jdbcDao.closeRs();
		jdbcDao.closeStmt();
		jdbcDao.closeConn();
		return list;
	}
	
	public int getAddresssListUserPage(String id,String group_id) {
		String sql = "select count(*) from t_addressList_User where group_id="+group_id+" and id<"+id+"";
		ResultSet rs = jdbcDao.executeQuary(sql);
		int i = 0;
		try {
			rs.next();
			i = rs.getInt(0);
			
			i = i/AddressListUserPageBo.pagenum;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 1;
	}
	
	public String FirstDelectAddressListUser(String id) {
		SimpleExpression a = Restrictions.eq("id", id);
		DetachedCriteria criteria = DetachedCriteria.forClass(AddresslistSend.class);
		DetachedCriteria criteria1 = criteria.createCriteria("addressListUser");
		criteria1.add(a);
		List list = qbcDao.getCriteria(criteria);
		String str = "no";
		if (list.size() != 0) {
			str = "have";
		}
		return str;
	}
	
	public int deleteAddressListUser(String id,int page,String moduleType) {
		String sql = "delete from t_addressList_User where id='"+id+"'";
		if (moduleType.equals("0")) {
			AddressListUser listUser = this.getAddresssListUserById(id);
			String maxyuanSort = this.getMaxUserSort(listUser.getAddressListGroup().getId().toString());
			int i = Integer.valueOf(listUser.getUserSort());
			int j = Integer.valueOf(maxyuanSort);
			jdbcDao.executeUpdate(sql);
			this.sortXianGtYuan(i, j-1,listUser.getAddressListGroup().getId().toString());
		}else{
			jdbcDao.executeUpdate(sql);
		}
		
		return page;
	}
	
	public void delete(String id) {
		String sql = "delete from t_addressList_User where id='"+id+"'";
		jdbcDao.executeUpdate(sql);
	}
	
	public List selectListUserName(String name,Short type,String user_id) {
		String sql = "select listuser.id,listuser.userName,listuser.mobile,listuser.company,listuser.sector,listuser.companyPhone,listgroup.groupName,listuser.createTime from t_addressList_User as listuser inner join t_addressList_Group as listgroup on listuser.group_id=listgroup.id where listuser.userName like '%"+name+"%' and listgroup.moduleType="+type+" and listgroup.user_id='"+user_id+"' group by listgroup.groupName,listuser.group_id,listuser.id,listuser.userName,listuser.mobile,listuser.company,listuser.sector,listuser.companyPhone,listuser.createTime order by listuser.createTime asc";
		ResultSet rs = jdbcDao.executeQuary(sql);
		List<AddressListUser> list = new ArrayList();
		
		try {
			while(rs.next()){
				AddressListUser user = new AddressListUser();
				String id = rs.getString("id");
				String userName = rs.getString("userName");
				String mobile = rs.getString("mobile");
				String company = rs.getString("company");
				String sector = rs.getString("sector");
				String companyPhone = rs.getString("companyPhone");
				String blog = rs.getString("groupName");
				
				user.setId(id);
				user.setUserName(userName);
				user.setMobile(mobile);
				user.setCompany(company);
				user.setSector(sector);
				user.setCompanyPhone(companyPhone);
				user.setBlog(blog);
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		jdbcDao.closeRs();
		jdbcDao.closeStmt();
		jdbcDao.closeConn();
		return list;
	}
	
	public List selectHighListUser(String lie,String value,Short type,String user_id) {
		String sql = "select listuser.id,listuser.userName,listuser.mobile,listuser.company,listuser.sector,listuser.companyPhone,listgroup.groupName,listuser.createTime from t_addressList_User as listuser inner join t_addressList_Group as listgroup on listuser.group_id=listgroup.id where listuser."+lie+" like '%"+value+"%' and listgroup.moduleType="+type+" and listgroup.user_id='"+user_id+"' group by listgroup.groupName,listuser.group_id,listuser.id,listuser.userName,listuser.mobile,listuser.company,listuser.sector,listuser.companyPhone,listuser.createTime order by listuser.createTime asc";
		if (lie.equals("birthday")) {
			Date date = null;
			Date date1 = null;
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			try {
				date = sdf.parse(value);
				date1 = DayFormat.afterNDay(date, -1);
				value = value+" 23:59:59";
				sql = "select listuser.id,listuser.userName,listuser.mobile,listuser.company,listuser.sector,listuser.companyPhone,listgroup.groupName,listuser.createTime from t_addressList_User as listuser inner join t_addressList_Group as listgroup on listuser.group_id=listgroup.id where listuser.birthday between '"+sdf.format(date1)+" 00:00:00' and '"+value+"' and listgroup.moduleType="+type+" and listgroup.user_id='"+user_id+"' group by listgroup.groupName,listuser.group_id,listuser.id,listuser.userName,listuser.mobile,listuser.company,listuser.sector,listuser.companyPhone,listuser.createTime order by listuser.createTime asc";
			} catch (Exception e) {
				// TODO Auto-generated catch block
				sql = "select listuser.id,listuser.userName,listuser.mobile,listuser.company,listuser.sector,listuser.companyPhone,listgroup.groupName,listuser.createTime from t_addressList_User as listuser inner join t_addressList_Group as listgroup on listuser.group_id=listgroup.id where listgroup.moduleType="+type+" and listgroup.user_id='"+user_id+"' group by listgroup.groupName,listuser.group_id,listuser.id,listuser.userName,listuser.mobile,listuser.company,listuser.sector,listuser.companyPhone,listuser.createTime order by listuser.createTime asc";
			}
		}
		ResultSet rs = jdbcDao.executeQuary(sql);
		List<AddressListUser> list = new ArrayList();
		
		try {
			while(rs.next()){
				AddressListUser user = new AddressListUser();
				String id = rs.getString("id");
				String userName = rs.getString("userName");
				String mobile = rs.getString("mobile");
				String company = rs.getString("company");
				String sector = rs.getString("sector");
				String companyPhone = rs.getString("companyPhone");
				String blog = rs.getString("groupName");
				
				user.setId(id);
				user.setUserName(userName);
				user.setMobile(mobile);
				user.setCompany(company);
				user.setSector(sector);
				user.setCompanyPhone(companyPhone);
				user.setBlog(blog);
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		jdbcDao.closeRs();
		jdbcDao.closeStmt();
		jdbcDao.closeConn();
		return list;
	}
	
	public void CopyListUser(String copyid,String copygroup) {
		String procedure = "{call dbo.TJSOA_AddressList_Copy(?,?,?)}";
		CallableStatement cstmt = jdbcDao.returnCstmt(procedure);
		try {
			cstmt.setString(1, copyid);
			cstmt.setString(2, copygroup);
			cstmt.setString(3, java.util.UUID.randomUUID().toString().replace("-", ""));
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		jdbcDao.cunchu(cstmt);
		try {
			cstmt.close();
			jdbcDao.closeConn();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public void MoveListUser(String copyid,String copygroup) {
		String sql = "update t_addressList_User set group_id='"+copygroup+"' where id in("+copyid+")";
		jdbcDao.executeUpdate(sql);
		jdbcDao.closeStmt();
		jdbcDao.closeConn();
	}
	
	public void updatesUserId(String mubiao_userIds,String tongbuUserid) {
		String sql = "update t_addressList_User set state='0' where tjsuser_id in("+mubiao_userIds+") and user_id='"+tongbuUserid+"'";
		jdbcDao.executeUpdate(sql);
	}
	
	public void updates(String id,String userName,String mobile,String email,String company,String sector,String duty,String companyPhone,String companyFax,String companyAddress,String companyPostal,String homeAddress,String homePostal,String homePhone,String birthday,String qq,String msn,String blog,String remarks) {
		String sql = "update t_addressList_User set userName='"+userName+"',mobile='"+mobile+"',email='"+email+"',company='"+company+"',sector='"+sector+"',duty='"+duty+"',companyPhone='"+companyPhone+"',companyFax='"+companyFax+"',companyAddress='"+companyFax+"',companyPostal='"+companyPostal+"',homeAddress='"+homeAddress+"',homePostal='"+homePostal+"',homePhone='"+homePhone+"',birthday='"+birthday+"',qq='"+qq+"',msn='"+msn+"',blog='"+blog+"',remarks='"+remarks+"' where user_id='"+id+"' and state='1'";
		String sql1 = "update TjsUser set userName='"+userName+"',mobile='"+mobile+"',email='"+email+"',company='"+company+"',sector='"+sector+"',duty='"+duty+"',companyPhone='"+companyPhone+"',companyFax='"+companyFax+"',companyAddress='"+companyFax+"',companyPostal='"+companyPostal+"',homeAddress='"+homeAddress+"',homePostal='"+homePostal+"',homePhone='"+homePhone+"',birthday='"+birthday+"',qq='"+qq+"',msn='"+msn+"',blog='"+blog+"',remarks='"+remarks+"' where id='"+id+"'";
		if (birthday == null || "++".equals(birthday)) {
			sql = "update t_addressList_User set userName='"+userName+"',mobile='"+mobile+"',email='"+email+"',company='"+company+"',sector='"+sector+"',duty='"+duty+"',companyPhone='"+companyPhone+"',companyFax='"+companyFax+"',companyAddress='"+companyFax+"',companyPostal='"+companyPostal+"',homeAddress='"+homeAddress+"',homePostal='"+homePostal+"',homePhone='"+homePhone+"',qq='"+qq+"',msn='"+msn+"',blog='"+blog+"',remarks='"+remarks+"' where user_id='"+id+"' and state='1'";
			sql1 = "update TjsUser set userName='"+userName+"',mobile='"+mobile+"',email='"+email+"',company='"+company+"',sector='"+sector+"',duty='"+duty+"',companyPhone='"+companyPhone+"',companyFax='"+companyFax+"',companyAddress='"+companyFax+"',companyPostal='"+companyPostal+"',homeAddress='"+homeAddress+"',homePostal='"+homePostal+"',homePhone='"+homePhone+"',qq='"+qq+"',msn='"+msn+"',blog='"+blog+"',remarks='"+remarks+"' where id='"+id+"'";
		}
		jdbcDao.executeUpdate(sql);
		jdbcDao.executeUpdate(sql1);
		jdbcDao.closeStmt();
		jdbcDao.closeConn();
	}
	
	public void updatesTongBu(String id,String userName,String mobile,String email,String company,String sector,String duty,String companyPhone,String companyFax,String companyAddress,String companyPostal,String homeAddress,String homePostal,String homePhone,String birthday,String qq,String msn,String blog,String remarks) {
		String sql = "update t_addressList_User set userName='"+userName+"',mobile='"+mobile+"',email='"+email+"',company='"+company+"',sector='"+sector+"',duty='"+duty+"',companyPhone='"+companyPhone+"',companyFax='"+companyFax+"',companyAddress='"+companyFax+"',companyPostal='"+companyPostal+"',homeAddress='"+homeAddress+"',homePostal='"+homePostal+"',homePhone='"+homePhone+"',birthday='"+birthday+"',qq='"+qq+"',msn='"+msn+"',blog='"+blog+"',remarks='"+remarks+"',state='1' where user_id='"+id+"'";
		if (birthday == null || "++".equals(birthday)) {
			sql = "update t_addressList_User set userName='"+userName+"',mobile='"+mobile+"',email='"+email+"',company='"+company+"',sector='"+sector+"',duty='"+duty+"',companyPhone='"+companyPhone+"',companyFax='"+companyFax+"',companyAddress='"+companyFax+"',companyPostal='"+companyPostal+"',homeAddress='"+homeAddress+"',homePostal='"+homePostal+"',homePhone='"+homePhone+"',qq='"+qq+"',msn='"+msn+"',blog='"+blog+"',remarks='"+remarks+"',state='1',birthday=null where user_id='"+id+"'";
		}
		jdbcDao.executeUpdate(sql);
		jdbcDao.closeStmt();
		jdbcDao.closeConn();
	}
	
	public String getMaxUserSort(String groupid) {
		Connection conn = jdbcDao.getConn();
		String sql = "select Max(userSort) from t_addressList_User where group_id=?";
		PreparedStatement statement;
		ResultSet rs = null;
		String i = "";
		try {
			statement = conn.prepareStatement(sql);
			statement.setString(1, groupid);
			rs = statement.executeQuery();
			if(rs.next()){
				i = String.valueOf(rs.getInt(1)+1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
		
	}
	
	public void sortYuanGtXian(int yuan,int xian,String group_id) {
		Connection conn = jdbcDao.getConn();
		String sql = "update t_addressList_User set userSort=userSort+1 where group_id='"+group_id+"' and userSort between ? and ?";
		PreparedStatement statement;
		try {
			statement = conn.prepareStatement(sql);
			statement.setInt(1, xian);
			statement.setInt(2, yuan-1);
			statement.executeUpdate();
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void sortXianGtYuan(int yuan,int xian,String group_id) {
		Connection conn = jdbcDao.getConn();
		String sql = "update t_addressList_User set userSort=userSort-1 where group_id='"+group_id+"' and userSort between ? and ?";
		PreparedStatement statement;
		try {
			statement = conn.prepareStatement(sql);
			statement.setInt(1, yuan+1);
			statement.setInt(2, xian);
			statement.executeUpdate();
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public List getAddressListUserTongBu(TSystemUser tjsuser_id,Integer user_id) {
		SimpleExpression a = Restrictions.eq("tjsUser", tjsuser_id);
		SimpleExpression b = Restrictions.eq("userId", user_id);
		SimpleExpression c = Restrictions.eq("state", Byte.valueOf("0"));
		SimpleExpression d = Restrictions.eq("state", Byte.valueOf("1"));
		LogicalExpression e = Restrictions.or(c, d);
		DetachedCriteria criteria = DetachedCriteria.forClass(AddressListUser.class);
		criteria.add(a);
		criteria.add(b);
		criteria.add(e);
		
		List list = qbcDao.getCriteria(criteria);
		return list;
	}
	
}
